﻿using API_lib.Exceptions;
using API_lib.Util;
using System;
using System.Collections.Generic;
using System.Data.SQLite;

namespace API_lib.Persistence.DAO
{
    /// <summary>
    /// Thin data access object to become a parent of all DAOs,
    /// gives database access and some simple sql functions.
    /// </summary>
    public abstract class PureDAO
    {
        /// <summary>
        /// Database name that will be used.
        /// </summary>
        public const string DB_NAME = "test.db";

        /// <summary>
        /// Creates a connection to the database.
        /// </summary>
        /// <returns>SQLiteConnection connection object.</returns>
        private SQLiteConnection getConnection()
        {
            SQLiteConnection conn = new SQLiteConnection();
            conn.ConnectionString = "Data Source=" + DB_NAME;
            conn.Open();

            return conn;
        }

        /// <summary>
        /// Closes the database connection.
        /// </summary>
        /// <param name="conn">The connection to close.</param>
        protected void closeConnection(SQLiteConnection conn)
        {
            conn.Close();
        }

        /// <summary>
        /// Executes an sql statement.
        /// </summary>
        /// <param name="sqlStatement">The SQL statement to execute.</param>
        /// <exception cref="DAOException">If database error occures.</exception>
        public void executeSqlSQLStatement(String sqlStatement)
        {
            if ((sqlStatement == null) || ("" == sqlStatement))
            {
                DAOException e = new DAOException("SqlStatement parameter is null or empty."); 
                ErrorLogger.logError(e);
                throw e;
            }

            SQLiteConnection conn = null;
            SQLiteCommand stm;
            
            try
            {
                conn = getConnection();

                stm = new SQLiteCommand(conn);
                stm.CommandText = sqlStatement;
                stm.ExecuteNonQuery();           
            }
            catch (Exception e)
            {
                ErrorLogger.logError(e, "SQL error while trying to execute statement.");
                throw new DAOException("SQL error while trying to execute statement.", e);
            }
            finally
            {
                closeConnection(conn);
            }
        }

        /// <summary>
        /// Executes an sql statement.
        /// </summary>
        /// <param name="sqlStatements">array of statements to execute.</param>
        /// <exception cref="DAOException">If database error occures.</exception>
	    public void executeSqlStatements(String[] sqlStatements)
        {
            if(sqlStatements == null)
            {
                DAOException e = new DAOException("SqlStatements parameter is null.");
                ErrorLogger.logError(e);
                throw e;
            }

            SQLiteConnection conn = null;
            SQLiteCommand stm;

            try
            {
                conn = getConnection();
               
                stm = new SQLiteCommand(conn);
              
                foreach(String statement in sqlStatements)
                {
                    stm.CommandText = statement;
                    stm.ExecuteNonQuery();
                }
            }
            catch (Exception e)
            {
                ErrorLogger.logError(e, "SQL error while trying to execute statement.");
                throw new DAOException("SQL error while trying to execute statement.", e);
            }
            finally
            {
                closeConnection(conn);
            }
        }

        /// <summary>
        /// Returns true if statements execution has result.
        /// </summary>
        /// <param name="sqlStatement">Statement to execute.</param>
        /// <exception cref="DAOException">if parameter wrong or sql exception occured.</exception>
	    public Boolean isSqlStatementResult (String sqlStatement)
        {
            SQLiteConnection conn = null;
            SQLiteCommand stm;
            SQLiteDataReader rs;
            Boolean result = false;

            try
            {
                conn = getConnection();

                stm = new SQLiteCommand(conn);
                stm.CommandText = sqlStatement;

                rs = stm.ExecuteReader();

                if (rs.Read())
                {
                    result = true;
                }
            }
            catch (Exception e)
            {
                ErrorLogger.logError(e, "SQL error while reading database.");
                throw new DAOException("SQL error while reading database.", e);
            }
            finally
            {
                closeConnection(conn);
            }

            return result;
        }

        ///<summary>Returns string value from result set specified by columnName parameter.</summary>
        ///<param name="sqlStatement">Statement to execute.</param>
        ///<param name="columnName">Name of column in result set whose value will be returned.</param>
        ///<returns>String value from result set specified by columnName parameter.</returns>
        ///<exception cref="DAOException">If parameter wrong or sql exception occured.</exception>
        public String getStringSqlResult(String sqlStatement, String columnName)
        {
            SQLiteConnection conn = null;
            SQLiteCommand stm;
            SQLiteDataReader rs;
            String result = null;

            try
            {
                conn = getConnection();

                stm = new SQLiteCommand(conn);
                stm.CommandText = sqlStatement;

                rs = stm.ExecuteReader();

                if (rs.Read())
                {
                    result = Convert.ToString(rs[columnName]);
                }
            }
            catch (Exception e)
            {
                ErrorLogger.logError(e, "SQL error while reading database.");
                throw new DAOException("SQL error while reading database.", e);
            }
            finally
            {
                closeConnection(conn);
            }

            return result;
        }

        ///<summary>Returns string list from result set specified by columnName parameter.</summary>
        ///<param name="sqlStatement">Statement to execute.</param>
        ///<param name="columnName">Name of column in result set whose values will be returned.</param>
        ///<returns>String value from result set specified by columnName parameter.</returns>
        ///<exception cref="DAOException">If a parameter is wrong or sql exception occured.</exception>
        public List<String> getStringListSqlResult(String sqlStatement, String columnName)
        {
            SQLiteConnection conn = null;
            SQLiteCommand stm;
            SQLiteDataReader rs;
            List<String> result = new List<String>();

            try
            {
                conn = getConnection();

                stm = new SQLiteCommand(conn);
                stm.CommandText = sqlStatement;

                rs = stm.ExecuteReader();

                while (rs.Read())
                {
                    result.Add(Convert.ToString(rs[columnName]));
                }
            }
            catch (Exception e)
            {
                ErrorLogger.logError(e, "SQL error while reading database.");
                throw new DAOException("SQL error while reading database.", e);
            }
            finally
            {
                closeConnection(conn);
            }

            return result;
        }

        ///<summary>Returns integer value from result set specified by columnName parameter.</summary>
        ///<param name="sqlStatement">Statement to execute.</param>
        ///<param name="columnName">Name of column in result set whose values will be returned.</param>
        ///<returns>Int value from result set specified by columnName parameter.</returns>
        ///<exception cref="DAOException">If a parameter is wrong or sql exception occured.</exception>
        public int getIntegerSqlResult(String sqlStatement, String columnName)
        {
            SQLiteConnection conn = null;
            SQLiteCommand stm;
            SQLiteDataReader rs;
            int result = 0;

            try
            {
                conn = getConnection();

                stm = new SQLiteCommand(conn);
                stm.CommandText = sqlStatement;

                rs = stm.ExecuteReader();

                if (rs.Read())
                {
                    result = Convert.ToInt32(rs[columnName]);
                }
            }
            catch (Exception e)
            {
                ErrorLogger.logError(e, "SQL error while reading database.");
                throw new DAOException("SQL error while reading database.", e);
            }
            finally
            {
                closeConnection(conn);
            }

            return result;
        }

        ///<summary>Returns int list from result set specified by columnName parameter.</summary>
        ///<param name="sqlStatement">Statement to execute.</param>
        ///<param name="columnName">Name of column in result set whose values will be returned.</param>
        ///<returns>Int value from result set specified by columnName parameter.</returns>
        ///<exception cref="DAOException">If a parameter is wrong or sql exception occured.</exception>
        public List<int> getIntegerListSqlResult(String sqlStatement, String columnName)
        {
            SQLiteConnection conn = null;
            SQLiteCommand stm;
            SQLiteDataReader rs = null;
            List<int> result = new List<int>();

            try
            {
                conn = getConnection();

                stm = new SQLiteCommand(conn);
                stm.CommandText = sqlStatement;

                rs = stm.ExecuteReader();

                while (rs.Read())
                {
                    result.Add(Convert.ToInt32(rs[columnName]));
                }
            }
            catch (Exception e)
            {
                ErrorLogger.logError(e, "SQL error while reading database.");
                throw new DAOException("SQL error while reading database.", e);
            }
            finally
            {
                rs.Close();
                closeConnection(conn);
            }

            return result;
        }

        /// <summary>
        /// Returns an SQLiteDataReader object containing the result of the query.
        /// </summary>
        /// <param name="sqlStatement">The statement to execute.</param>
        /// <returns>SQLiteDataReader containing the result.</returns>
        public SQLiteDataReader getSQLiteDataReaderResult(String sqlStatement)
        {
            SQLiteConnection conn = null;
            SQLiteCommand stm;
            SQLiteDataReader rs;

            try
            {
                conn = getConnection();

                stm = new SQLiteCommand(conn);
                stm.CommandText = sqlStatement;

                rs = stm.ExecuteReader();
            }
            catch (Exception e)
            {
                ErrorLogger.logError(e, "SQL error while reading database.");
                throw new DAOException("SQL error while reading database.", e);
            }
            finally
            {
                // only the reader is closed in the getRowAsMo method
                // TODO close it properly
               // closeConnection(conn);
            }

            return rs;
        }
    }
}
